<?php

namespace App\Console\Commands;

use App\Models\Game\Center\Action;
use App\Models\Game\Center\AppSource;
use App\Models\Game\Center\Item;
use App\Models\Game\Center\PayOrder;
use App\Models\Game\Center\ThirdExchangeRecord;
use App\Models\Game\Center\WithdrawOrder;
use App\Models\Mongo\DailyLog\DayCpaCost;
use App\Models\Mongo\DailyLog\HourStat;
use App\Models\Mongo\PlayerCache\GoodsModifyRecord;
use App\Models\Mongo\PlayerCache\SceneRunningWater;
use App\Services\CalcService;
use Carbon\Carbon;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class CalcHourStat extends Command
{
    protected $signature = 'calc:hour-stat {--hour= : 请输入运行日期+小时, 例如2021-01-01:09}';

    protected $description = '每小时数据统计';

    /**
     * @var Carbon
     */
    protected $hour;

    /**
     * @var Carbon
     */
    protected $hourStart;

    /**
     * @var Carbon
     */
    protected $hourEnd;

    public function __construct()
    {
        parent::__construct();
    }

    public function handle()
    {
        $this->hour = $this->option('hour') ? Carbon::createFromFormat('Y-m-d:H', $this->option('hour')) : now()->subHour();
        $this->hourStart = $this->hour->copy()->startOfHour();
        $this->hourEnd = $this->hour->copy()->endOfHour();

        // TODO new
        $pays = $this->getPay(); // 今日充值金额和人数
        $withdraws = $this->getWithdraw(); // 今日权益金额和人数
        $thirdExchanges = $this->getThirdExchange(); // 今日积分和人数
        $registerCounts = $this->getRegisterCount(); // 注册人数
        $loginCounts = $this->getLoginUserCount(); // 登录人数

        $withdrawFirsts = $this->getWithdrawFirst();
        $thirdExchangeFirsts = $this->getThirdExchangeFirst();
        $payFirstOldUsers = $this->getPayFirstOldUser();
        // $hbChanges = $this->getHbChange();// 权益卡变动
        // $sceneRunningWaters = $this->getSceneRunningWater(); // 场次流水
        $cplCosts = $this->getCplCost();
        $cpaCosts = $this->getCpaCost();

        $appSources = AppSource::get();
        $hourStatAll = [];
        foreach ($appSources as $appSource) {
            $hourStat = HourStat::firstOrNew(['day' => $this->hour->toDateString(), 'source' => $appSource->id, 'hour' => $this->hour->hour]);
            $hourStatLast = HourStat::where(['day' => $this->hour->toDateString(), 'source' => $appSource->id, 'hour' => $this->hour->copy()->subHour()->hour])->first();

            list($hourStat->pay, $hourStat->payCnt, $hourStat->payUser) = $pays[$appSource->id] ?? [0, 0, 0];
            list($hourStat->withdraw, $hourStat->withdrawCnt, $hourStat->withdrawUser) = $withdraws[$appSource->id] ?? [0, 0, 0];
            list($hourStat->withdrawFirstAll, $hourStat->withdrawFirstUserAll) = $withdrawFirsts[$appSource->id] ?? [0, 0];

            list($hourStat->thirdExchange, $hourStat->thirdExchangeCnt, $hourStat->thirdExchangeUser) = $thirdExchanges[$appSource->id] ?? [0, 0, 0];
            list($hourStat->thirdExchangeFirstAll, $hourStat->thirdExchangeFirstUserAll) = $thirdExchangeFirsts[$appSource->id] ?? [0, 0];

            list($hourStat->payFirstOldUser24All, $hourStat->payFirstOldUser48All) = $payFirstOldUsers[$appSource->id] ?? [0, 0];// 24小时内首次支付 和 48小时内首次支付

            $hourStat->registerUserAll = $registerCounts[$appSource->id] ?? 0;
            $hourStat->loginUserAll = $loginCounts[$appSource->id] ?? 0;
            $hourStat->cplCost = $cplCosts[$appSource->id] ?? 0;
            $hourStat->cpaCostAll = $cpaCosts[$appSource->id] ?? 0;
            // $hourStat->hbChangeAll = $hbChanges[$appSource->id] ?? 0;
            // $hourStat->sceneRunningWaterAll = $sceneRunningWaters[$appSource->id] ?? 0;
            // $hourStat->hbChangeAll = 0;
            $hourStat->sceneRunningWaterAll = 0;

            // 需要减去当天已有数据
            if ($this->hour->hour > 0) {
                $hourStat->registerUser = $hourStat->registerUserAll - ($hourStatLast->registerUserAll ?? 0);
                $hourStat->loginUser = $hourStat->loginUserAll - ($hourStatLast->loginUserAll ?? 0);
                // $hourStat->hbChange = $hourStat->hbChangeAll - ($hourStatLast->hbChangeAll ?? 0);
                $hourStat->sceneRunningWater = $hourStat->sceneRunningWaterAll - ($hourStatLast->sceneRunningWaterAll ?? 0);
                $hourStat->cpaCost = $hourStat->cpaCostAll - ($hourStatLast->cpaCostAll ?? 0);
                $hourStat->payFirstOldUser24 = $hourStat->payFirstOldUser24All - ($hourStatLast->payFirstOldUser24All ?? 0);
                $hourStat->payFirstOldUser48 = $hourStat->payFirstOldUser48All - ($hourStatLast->payFirstOldUser48All ?? 0);
            } else {
                $hourStat->registerUser = $hourStat->registerUserAll;
                $hourStat->loginUser = $hourStat->loginUserAll;
                // $hourStat->hbChange = $hourStat->hbChangeAll;
                $hourStat->sceneRunningWater = $hourStat->sceneRunningWaterAll;
                $hourStat->cpaCost = $hourStat->cpaCostAll;
                $hourStat->payFirstOldUser24 = $hourStat->payFirstOldUser24All;
                $hourStat->payFirstOldUser48 = $hourStat->payFirstOldUser48All;
            }
            $hourStat->save();

            $hourStatAll[] = $hourStat->toArray();
        }
        $this->storeAllSource($hourStatAll);
    }

    /**
     * 对存储的数据进行汇总
     * 部分数据无法区分渠道, 在这里统计
     */
    public function storeAllSource(array $hourStatAll)
    {
        $dateString = $this->hour->toDateString();

        // 对已有的渠道数据进行合并
        $fields = (new HourStat())->getFillable();
        $sumData = [];
        foreach ($hourStatAll as $stat) {
            foreach ($fields as $field) {
                if (in_array($field, ['day', 'hour', 'source'])) continue;
                $sumData[$field] = ($sumData[$field]??0) + ($stat[$field] ?? 0);
            }
        }

        $hourStat = new HourStat($sumData);
        $hourStat->day = $this->hour->toDateString();
        $hourStat->hour = $this->hour->hour;
        $hourStat->source = 0;
        // $hourStat->hbChangeAll = $this->getHbChange();// 权益卡变动
        $hourStat->hbToBulletCost = abs($this->getHbToBulletCost());// 权益兑换字段消耗
        $hourStat->sceneRunningWaterAll = $this->getSceneRunningWater(); // 场次流水

        $hourStatLast = HourStat::where(['day' => $this->hour->toDateString(), 'source' => 0, 'hour' => $this->hour->copy()->subHour()->hour])->first();

        if ($this->hour->hour > 0) {
            $hourStat->registerUser = $hourStat->registerUserAll - ($hourStatLast->registerUserAll ?? 0);
            $hourStat->loginUser = $hourStat->loginUserAll - ($hourStatLast->loginUserAll ?? 0);
            // $hourStat->hbChange = $hourStat->hbChangeAll - ($hourStatLast->hbChangeAll ?? 0);
            $hourStat->sceneRunningWater = $hourStat->sceneRunningWaterAll - ($hourStatLast->sceneRunningWaterAll ?? 0);
            $hourStat->cpaCost = $hourStat->cpaCostAll - ($hourStatLast->cpaCostAll ?? 0);
            $hourStat->payFirstOldUser24 = $hourStat->payFirstOldUser24All - ($hourStatLast->payFirstOldUser24All ?? 0);
            $hourStat->payFirstOldUser48 = $hourStat->payFirstOldUser48All - ($hourStatLast->payFirstOldUser48All ?? 0);
        } else {
            $hourStat->registerUser = $hourStat->registerUserAll;
            $hourStat->loginUser = $hourStat->loginUserAll;
            // $hourStat->hbChange = $hourStat->hbChangeAll;
            $hourStat->sceneRunningWater = $hourStat->sceneRunningWaterAll;
            $hourStat->cpaCost = $hourStat->cpaCostAll;
            $hourStat->payFirstOldUser24 = $hourStat->payFirstOldUser24All;
            $hourStat->payFirstOldUser48 = $hourStat->payFirstOldUser48All;
        }
        HourStat::updateOrCreate(['day' => $dateString, 'hour' => $this->hour->hour, 'source' => 0], $hourStat->toArray());
    }

    /**
     * 今日充值数据
     * @return int[]
     */
    private function getPay(): array
    {
        $timeRange = [$this->hourStart->toDateTimeString(), $this->hourEnd->toDateTimeString()];
        $data = PayOrder::ofDatetimeRange('payed_at', $timeRange)
            ->selectRaw('SUM(price) AS price, COUNT(id) AS cnt, COUNT(DISTINCT player_id) AS player, source')
            ->groupBy('source')
            ->get()
            ->keyBy('source')
            ->toArray();
        return array_map(function($v) {
            return [$v['price'], $v['cnt'], $v['player']];
        }, $data);
    }

    /**
     * 今日权益数据
     * @return int[]
     */
    private function getWithdraw(): array
    {
        $timeRange = [$this->hourStart->toDateTimeString(), $this->hourEnd->toDateTimeString()];
        $data = WithdrawOrder::ofDatetimeRange('updated_at', $timeRange)
            ->whereStatus(WithdrawOrder::STATUS_SUCCESS)
            ->selectRaw('SUM(money) AS price ,COUNT(id) AS cnt, COUNT(DISTINCT player_id) AS player, source')
            ->groupBy('source')
            ->get()
            ->keyBy('source')
            ->toArray();
        return array_map(function($v) {
            return [$v['price'], $v['cnt'], $v['player']];
        }, $data);
    }

    /**
     * 首日权益 数据
     * @return array
     */
    private function getWithdrawFirst(): array
    {
        $timeRange = [$this->hourStart->copy()->startOfDay()->toDateTimeString(), $this->hourEnd->toDateTimeString()];
        $lists = WithdrawOrder::selectRaw("source, SUM(money) AS money, COUNT(DISTINCT player_id) AS cnt")
            ->where('status', WithdrawOrder::STATUS_SUCCESS)
            ->whereBetween('updated_at', $timeRange)
            ->whereBetween('register_at', $timeRange)
            ->groupBy('source')
            ->get()
            ->keyBy('source')
            ->toArray();
        foreach ($lists as $source => $v) {
            $lists[$source] = [$v['money'] ?? 0, $v['cnt'] ?? 0];
        }
        return $lists;
    }

    /**
     * 今日积分数据
     * @return int[]
     */
    private function getThirdExchange(): array
    {
        $timeRange = [$this->hourStart->toDateTimeString(), $this->hourEnd->toDateTimeString()];
        $data = ThirdExchangeRecord::ofDatetimeRange('created_at', $timeRange)
            ->whereStatus(ThirdExchangeRecord::STATUS_SUCCESS)
            ->selectRaw('SUM(cost) AS price ,COUNT(id) AS cnt, COUNT(DISTINCT player_id) AS player, source')
            ->groupBy('source')
            ->get()
            ->keyBy('source')
            ->toArray();
        return array_map(function($v) {
            return [$v['price'], $v['cnt'], $v['player']];
        }, $data);
    }

    /**
     * 首日积分 数据
     * @return array
     */
    private function getThirdExchangeFirst(): array
    {
        $timeRange = [$this->hourStart->copy()->startOfDay()->toDateTimeString(), $this->hourEnd->toDateTimeString()];
        $lists = ThirdExchangeRecord::selectRaw("source, SUM(cost) AS money, COUNT(DISTINCT player_id) AS cnt")
            ->where('status', ThirdExchangeRecord::STATUS_SUCCESS)
            ->whereBetween('updated_at', $timeRange)
            ->whereBetween('register_at', $timeRange)
            ->groupBy('source')
            ->get()
            ->keyBy('source')
            ->toArray();
        foreach ($lists as $source => $v) {
            $lists[$source] = [$v['money'] ?? 0, $v['cnt'] ?? 0];
        }
        return $lists;
    }

    /**
     * 今日注册数量
     * @return array
     */
    private function getRegisterCount(): array
    {
        return Action::where('day', $this->hourEnd->format('Ymd'))
            ->whereType(Action::TYPE_REGISTER)
            ->selectRaw('source, COUNT(DISTINCT user_id) AS cnt')
            ->groupBy('source')
            ->get()
            ->pluck('cnt', 'source')
            ->all();
    }

    /**
     * 登录数量(当日累计)
     * @return array
     */
    private function getLoginUserCount(): array
    {
        return Action::where('day', $this->hourEnd->format('Ymd'))
            ->whereType(Action::TYPE_LOGIN)
            ->selectRaw('source, COUNT(DISTINCT user_id) AS cnt')
            ->groupBy('source')
            ->get()
            ->pluck('cnt', 'source')
            ->all();
    }

    /**
     * 权益卡变动数量(当日累计)
     * @return int
     */
    private function getHbChange(): int
    {
        return GoodsModifyRecord::ofDatetimeRange('create_at', [$this->hourStart, $this->hourEnd])
            ->ofFieldNumeric('item_id', Item::ITEM_ID_HB)
            ->sum('count');
    }

    /**
     * 红包券换字段消耗 (包含神抽)
     * @return int
     */
    private function getHbToBulletCost(): int
    {
        return GoodsModifyRecord::ofDatetimeRange('create_at', [$this->hourStart, $this->hourEnd])
            ->ofFieldNumeric('item_id', Item::ITEM_ID_HB)
            ->ofFieldNumeric('type1', [13, 50])
            ->sum('count');
    }

    /**
     * 场次流水(当日累计)
     * @return int
     */
    private function getSceneRunningWater(): int
    {
        return SceneRunningWater::ofFieldNumeric('day', (int) $this->hour->format('Ymd'))
            ->sum('consumeBullet');
    }

    /**
     * 前两天注册, 今天首次充值, 间隔时间在24小时内和48小时内的用户
     * @return array
     */
    private function getPayFirstOldUser(): array
    {
        $todayStart = $this->hourStart->copy()->startOfDay()->toDateTimeString();
        $todayEnd = $this->hourEnd->copy()->endOfDay()->toDateTimeString();

        $threeDayStart = $this->hourStart->copy()->startOfDay()->subDays(2);

        $sql = "SELECT source, SUM(CASE WHEN hours <= 24 THEN 1 ELSE 0 END) hour24, SUM(CASE WHEN hours <= 48 THEN 1 ELSE 0 END) hour48
                FROM (
                    SELECT source, player_id, register_at, payed_at, TIMESTAMPDIFF(HOUR, register_at, MIN(payed_at)) hours
                    FROM `tb_pay_order`
                    WHERE payed_at BETWEEN '{$threeDayStart}' AND '{$todayEnd}'
                    AND register_at BETWEEN '{$threeDayStart}' AND '{$todayEnd}'
                    AND status = 1
                    GROUP BY player_id
                    HAVING payed_at BETWEEN '{$todayStart}' AND '{$todayEnd}'
                ) t
                GROUP BY source;";
        $items = DB::connection('gameCenter')->select($sql);
        $all = [];
        foreach ($items as $k => $item) {
            $all[$item->source] = [$item->hour24, $item->hour48];
        }
        return $all;
    }

    /**
     * CPL支出费用
     * @return array
     */
    public function getCplCost(): array
    {
        $dayStart = $this->hourStart->toDateTimeString();
        $dayEnd = $this->hourEnd->toDateTimeString();

        $where = "WHERE log.created_at BETWEEN '{$dayStart}' AND '{$dayEnd}'";

        $sql = "SELECT SUM(value * player) AS cost, source FROM (
                    SELECT config.value, COUNT(log.player_id) AS player, info.source FROM `tb_cpl_issue_config` AS config

                    LEFT JOIN tb_cpl_issue_user_log AS log
                    ON config.id = log.cpl_issue_config_id

                    LEFT JOIN tb_cpl_activity_issue AS info
                    ON config.cpl_activity_issue_id = info.id

                    {$where}
                    GROUP BY config.id
                ) AS tmp
                GROUP BY source";
        $costs = DB::connection('gameCenter')->select($sql);
        $all = [];
        foreach ($costs as $k => $cost) {
            $all[$cost->source] = (int) $cost->cost * 100;// 转换为分
        }
        return $all;
    }

    /**
     * 获取CPA花费 四舍五入取整
     * @return array
     */
    public function getCpaCost(): array
    {
        $dayCpaCost = new DayCpaCost();
        $cpaCosts = $dayCpaCost->getCpaCostActuals([$this->hourStart->copy()->startOfDay()->toDateString(), $this->hourEnd->copy()->endOfDay()->toDateString()]);
        return array_map(function ($v) {
            $v /= 100;
            return round($v) * 100;
        }, $cpaCosts);
    }

}
